﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MINERP.DAL.QC
{
    public  class QCDAL
    {
        public static DataTable GetProductList(string shopName, string customerName, string productName, DateTime  startTime, DateTime endTime)
        {
            DataTable dt = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("GetProductList");
            db.AddInParameter(dbCommand, "@shopName", DbType.String, shopName);
            db.AddInParameter(dbCommand, "@customerName", DbType.String, customerName);
            db.AddInParameter(dbCommand, "@productName", DbType.String, productName);
            db.AddInParameter(dbCommand, "@startTime", DbType.DateTime, startTime);
            db.AddInParameter(dbCommand, "@endTime", DbType.DateTime, endTime);
            dt = db.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }

        /// <summary>
        /// 获取打印信息
        /// </summary>
        /// <param name="inproductId"></param>
        /// <returns></returns>
        public DataTable GetPrintInfo(string inproductId)
        {

            Database db = null;

            DataSet dsPrint = null;
            try
            {
                db = DatabaseFactory.CreateDatabase();
                //string sqlCommand = "select OrderProductName,CtmName,OrderProductCode,shopName,PageNum,FetchTime,orderinfo.productNum as productNum  from orderInfo,Tb_OrderBag,Shop where orderInfo.OrderBagId=Tb_OrderBag.OrderBagId and  Tb_OrderBag.shopID=Shop.ShopId and orderInfo.ParentProductId=0 and  orderInfo.OrderProductCode='" + inproductId + "'";
                //string sqlCommand = "select OrderProductName,CtmName,OrderProductCode,shopName,sum(photoinfo.totalNum) PageNum,FetchTime from orderInfo,Tb_OrderBag,Shop,photoinfo where orderInfo.OrderBagId=Tb_OrderBag.OrderBagId and  Tb_OrderBag.shopID=Shop.ShopId and orderInfo.ParentProductId=0  and orderInfo.orderproductid =photoInfo.orderproductid and  orderInfo.OrderProductCode='" + inproductId + "' group by OrderProductName,CtmName,OrderProductCode,shopName,PageNum,FetchTime";
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.Append(" select OrderProductName,CtmName,OrderProductCode,shopName, isnull(sum(photoinfo.totalNum),orderinfo.productNum) PageNum,FetchTime ");
                sqlBuilder.Append(" from orderInfo  inner join Tb_OrderBag on orderInfo.OrderBagId=Tb_OrderBag.OrderBagId ");
                sqlBuilder.Append(" inner  join Shop on Tb_OrderBag.shopID=Shop.ShopId ");
                sqlBuilder.Append(" left join  photoinfo on  orderInfo.orderproductid =photoInfo.orderproductid ");
                sqlBuilder.Append(" where  orderInfo.OrderProductCode='" + inproductId + "' ");
                sqlBuilder.Append(" group by OrderProductName,CtmName,OrderProductCode,shopName,PageNum,FetchTime,orderinfo.productNum");

                DbCommand dbCommand = db.GetSqlStringCommand(sqlBuilder.ToString());
                dsPrint = db.ExecuteDataSet(dbCommand);

            }
            catch
            {

            }
            if (dsPrint == null)
            {
                return null;
            }
            else
            {
                return dsPrint.Tables[0];
            }
        }
        public static DataTable GetPhotoInfosByPhotoCode(string inPhotoCode)
        {
            Database db = null;

            DataSet dsPrint = null;
            try
            {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.Append(" SELECT PhotoBarCode,ph.Remark,CtmName,OrderProductName,shopName                                      ");
                sqlBuilder.Append(" FROM  dbo.photoInfo AS ph  INNER  JOIN dbo.orderInfo AS oi ON ph.OrderProductId=oi.OrderProductId    ");
                sqlBuilder.Append(" inner JOIN dbo.Tb_OrderBag AS ob ON ob.OrderBagId = oi.OrderBagId                                    ");
                sqlBuilder.Append(" INNER JOIN dbo.Shop AS shop ON shop.ShopId = ob.shopID AND  ph.PhotoBarCode='" + inPhotoCode + "' ");
                db = DatabaseFactory.CreateDatabase();
                //string sqlCommand = "select PhotoBarCode,PicPath,OrderProductCode,photoInfo.Remark as PhotoRemark,OrderProductName,CtmName,ShopName,ProductType,TotalNum,productNum from photoInfo,orderInfo,tb_orderbag,shop where orderInfo.orderProductCode='" + orderProductCode + "' and ParentProductId=0 and OrderInfo.orderProductId=photoInfo.orderProductId and orderinfo.orderbagid=tb_orderbag.orderbagid and tb_orderbag.shopid=shop.shopid";
                DbCommand dbCommand = db.GetSqlStringCommand(sqlBuilder.ToString());
                dsPrint = db.ExecuteDataSet(dbCommand);


                if (dsPrint.Tables[0].Rows.Count == 0)
                {
                    StringBuilder sqlBuilderBK = new StringBuilder();
                    sqlBuilder.Append(" SELECT PhotoBarCode,ph.Remark,CtmName,OrderProductName,shopName                                           ");
                    sqlBuilder.Append(" FROM  dbo.photoInfo_bk AS ph  INNER  JOIN dbo.orderInfo_bk AS oi ON ph.OrderProductId=oi.OrderProductId   ");
                    sqlBuilder.Append(" inner JOIN dbo.Tb_OrderBag AS ob ON ob.OrderBagId = oi.OrderBagId                                         ");
                    sqlBuilder.Append(" NNER JOIN dbo.Shop AS shop ON shop.ShopId = ob.shopID AND  ph.PhotoBarCode='" + inPhotoCode + "'                   ");
                    db = DatabaseFactory.CreateDatabase();
                    //string sqlCommand = "select PhotoBarCode,PicPath,OrderProductCode,photoInfo.Remark as PhotoRemark,OrderProductName,CtmName,ShopName,ProductType,TotalNum,productNum from photoInfo,orderInfo,tb_orderbag,shop where orderInfo.orderProductCode='" + orderProductCode + "' and ParentProductId=0 and OrderInfo.orderProductId=photoInfo.orderProductId and orderinfo.orderbagid=tb_orderbag.orderbagid and tb_orderbag.shopid=shop.shopid";
                    DbCommand dbCommandBK = db.GetSqlStringCommand(sqlBuilderBK.ToString());
                    dsPrint = db.ExecuteDataSet(dbCommand);

                }
            }
            catch
            {

            }
            if (dsPrint == null)
            {
                return null;
            }
            else
            {
                return dsPrint.Tables[0];
            }
        }
        public DataTable GetPhotoByOrderProductCode(string orderProductCode)
        {

            Database db = null;

            DataSet dsPrint = null;
            try
            {
                db = DatabaseFactory.CreateDatabase();
                string sqlCommand = "select PhotoBarCode,PicPath,OrderProductCode,photoInfo.Remark as PhotoRemark,OrderProductName,CtmName,ShopName,ProductType,TotalNum,productNum from photoInfo,orderInfo,tb_orderbag,shop where orderInfo.orderProductCode='" + orderProductCode + "' and ParentProductId=0 and OrderInfo.orderProductId=photoInfo.orderProductId and orderinfo.orderbagid=tb_orderbag.orderbagid and tb_orderbag.shopid=shop.shopid";
                DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
                dsPrint = db.ExecuteDataSet(dbCommand);
            }
            catch
            {

            }
            if (dsPrint == null)
            {
                return null;
            }
            else
            {
                return dsPrint.Tables[0];
            }
        }
    }
}
